package com.boss.thirteen.dao.impl;

import com.boss.common.util.DateUtils;
import com.boss.framework.dao.BaseDaoImpl;
import com.boss.framework.dto.PaginationDTO;
import com.boss.salary.constant.SystemDictConstant;
import com.boss.salary.constant.TableName;
import com.boss.salary.entity.AgencyInfo;
import com.boss.salary.entity.SalaryBaseItemize;
import com.boss.sys.ca.user.dto.UserDTO;
import com.boss.sys.utils.SysUtils;
import com.boss.thirteen.dao.IThirteenSalaryItemizeDao;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Repository
public class ThirteenSalaryItemizeDaoImpl extends BaseDaoImpl implements IThirteenSalaryItemizeDao {

    @Override
    public List<SalaryBaseItemize> querySalaryBaseItemizePage(PaginationDTO paginationDTO, Map<String, Object> queryParam) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select a.salary_base_id,d.salary_code salary_base_code,d.salary_name salary_base_name, ")
                .append(" a.person_state,b.name person_state_name,a.mof_div_code,a.agency_id,e.agency_code,e.agency_name, ")
                .append(" a.payee_acct_id,c.payee_acct_name,c.payee_acct_no,c.payee_acct_bank_name,a.remark ")
                .append(" from ").append(TableName.THIRTEEN_SALARY_ITEMIZE).append("  a ")
                .append(" LEFT JOIN ").append(TableName.BASE_DATA).append(" b on a.person_state = b.code and b.type = 'salary_type' and b.is_deleted='0' and b.mof_div_code = ?  ")
                .append(" LEFT JOIN ").append(TableName.PAYEE_ACCT).append(" c on a.payee_acct_id = c.id ")
                .append(" LEFT JOIN ").append(TableName.SALARY_BASEDATA).append(" d on a.salary_base_id = d.id ")
                .append(" LEFT JOIN ").append(TableName.AGENCY_INFO).append(" e on a.agency_id = e. id ")
                .append(" where a.is_deleted = 0 ");
        paramList.add(queryParam.get("mof_div_code"));
        if (!StringUtils.isEmpty(queryParam.get("childrenId"))) {
            sql.append(" and ").append(SysUtils.inSql("a.agency_id", (List<String>) queryParam.get("childrenId")));
        }
        if (!StringUtils.isEmpty(queryParam.get("salaryBaseId"))) {
            sql.append(" and a.salary_base_id = ? ");
            paramList.add(queryParam.get("salaryBaseId"));
        }
        if (!StringUtils.isEmpty(queryParam.get("personState"))) {
            sql.append(" and a.person_state = ? ");
            paramList.add(queryParam.get("personState"));
        }
        if (!StringUtils.isEmpty(queryParam.get("payeeAcctId"))) {
            sql.append(" and a.payee_acct_id = ? ");
            paramList.add(queryParam.get("payeeAcctId"));
        }
        if (!StringUtils.isEmpty(queryParam.get("payeeAcctBankName"))) {
            String payeeAcctBankName = queryParam.get("payeeAcctBankName").toString();
            sql.append(" and c.payee_acct_bank_name like concat( '%', ?, '%')");
            paramList.add(payeeAcctBankName);
        }
        sql.append(" order by a.person_state, a.create_time ");
        String querySql = this.boxUpSql(sql.toString());
        Object[] params = boxUpParam(paramList.toArray(), paginationDTO);
        return this.getjdbcTemplate().query(querySql, new BeanPropertyRowMapper<>(SalaryBaseItemize.class), params);
    }

    @Override
    public int querySalaryBaseItemizeCount(Map<String, Object> queryParam) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select count(*) ").append(" from ").append(TableName.THIRTEEN_SALARY_ITEMIZE).append("  a ")
        .append(" LEFT JOIN ").append(TableName.PAYEE_ACCT).append(" c on a.payee_acct_id = c.id ")
                .append(" where a.is_deleted = 0 ");
        if (!StringUtils.isEmpty(queryParam.get("childrenId"))) {
            sql.append(" and ").append(SysUtils.inSql("a.agency_id", (List<String>) queryParam.get("childrenId")));
        }
        if (!StringUtils.isEmpty(queryParam.get("salaryBaseId"))) {
            sql.append(" and a.salary_base_id = ? ");
            paramList.add(queryParam.get("salaryBaseId"));
        }
        if (!StringUtils.isEmpty(queryParam.get("personState"))) {
            sql.append(" and a.person_state = ? ");
            paramList.add(queryParam.get("personState"));
        }
        if (!StringUtils.isEmpty(queryParam.get("payeeAcctId"))) {
            sql.append(" and a.payee_acct_id = ? ");
            paramList.add(queryParam.get("payeeAcctId"));
        }
        if (!StringUtils.isEmpty(queryParam.get("payeeAcctBankName"))) {
            String payeeAcctBankName = queryParam.get("payeeAcctBankName").toString();
            sql.append(" and c.payee_acct_bank_name like concat( '%', ?, '%')");
            paramList.add(payeeAcctBankName);
        }
        return this.getjdbcTemplate().queryForObject(sql.toString(), paramList.toArray(), Integer.class);
    }

    @Override
    public List<SalaryBaseItemize> querySalaryBaseItemize(Map<String, Object> queryParam) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select a.salary_base_id,d.salary_code salary_base_code,d.salary_name salary_base_name, ")
                .append(" a.person_state,b.name person_state_name,a.mof_div_code,a.agency_id,e.agency_code,e.agency_name, ")
                .append(" a.payee_acct_id,c.payee_acct_name,c.payee_acct_no,c.payee_acct_bank_name,a.remark ")
                .append(" from ").append(TableName.THIRTEEN_SALARY_ITEMIZE).append("  a ")
                .append(" LEFT JOIN ").append(TableName.BASE_DATA).append(" b on a.person_state = b.code and b.type = 'salary_type' and b.mof_div_code = ? ")
                .append(" LEFT JOIN ").append(TableName.PAYEE_ACCT).append(" c on a.payee_acct_id = c.id ")
                .append(" LEFT JOIN ").append(TableName.SALARY_BASEDATA).append(" d on a.salary_base_id = d.id ")
                .append(" LEFT JOIN ").append(TableName.AGENCY_INFO).append(" e on a.agency_id = e. id ")
                .append(" where a.is_deleted = 0 ");
        paramList.add(queryParam.get("mof_div_code"));
        if (!StringUtils.isEmpty(queryParam.get("agencyId"))) {
            sql.append(" and a.agency_id = ? ");
            paramList.add(queryParam.get("agencyId"));
        }
        sql.append(" order by a.create_time ");
        return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper(SalaryBaseItemize.class), paramList.toArray());
    }

    @Override
    public List<Map<String, Object>> queryBaseDataList(Map<String, Object> queryParam) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select code,name from ").append(TableName.BASE_DATA).append(" where is_deleted = 0 ")
                .append(" and type = 'salary_type' and mof_div_code = ?");
        Object[] objects = new Object[] {
          queryParam.get("mof_div_code")
        };
        return super.getjdbcTemplate().queryForList(sql.toString(), objects);
    }

    @Override
    public List<Map<String, Object>> queryPayeeAcctList(UserDTO userDTO ) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select id,payee_acct_name payeeAcctName,show_name showName from ").append(TableName.PAYEE_ACCT).append(" where is_deleted = 0 ");
        sql.append(" and mof_div_code=? ");
        return super.getjdbcTemplate().queryForList(sql.toString(),userDTO.getProvince());
    }

    @Override
    public List<Map<String, Object>> querySalaryBasedataList(String agencyId,UserDTO userDTO) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select id,salary_name ,salary_code from ").append(TableName.SALARY_BASEDATA).append("   where salary_code='s1' and is_deleted = 0 and mof_div_code=? ");
        paramList.add(userDTO.getProvince());

        sql.append(" order by show_order");
        return super.getjdbcTemplate().queryForList(sql.toString(), paramList.toArray());
    }

    @Override
    public void insertSalaryBaseItemize(SalaryBaseItemize salaryBaseItemize,UserDTO userDTO) {
        StringBuilder sql = new StringBuilder();
        sql.append(" insert into ").append(TableName.THIRTEEN_SALARY_ITEMIZE);
        sql.append(" (id, create_time, update_time, salary_base_id, salary_base_code, ")
                .append(" salary_base_name, person_state, person_state_name, mof_div_code, agency_id, ")
                .append(" agency_code, agency_name, payee_acct_id, payee_acct_name, payee_acct_no, ")
                .append(" payee_acct_bank_name, is_deleted,remark) ")
                .append(" value (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ");
        Object[] params = new Object[]{
                salaryBaseItemize.getId(), salaryBaseItemize.getCreateTime(), salaryBaseItemize.getUpdateTime(),
                salaryBaseItemize.getSalaryBaseId(), salaryBaseItemize.getSalaryBaseCode(),
                salaryBaseItemize.getSalaryBaseName(), salaryBaseItemize.getPersonState(),
                salaryBaseItemize.getPersonStateName(), userDTO.getProvince(), salaryBaseItemize.getAgencyId(),
                salaryBaseItemize.getAgencyCode(), salaryBaseItemize.getAgencyName(), salaryBaseItemize.getPayeeAcctId(),
                salaryBaseItemize.getPayeeAcctName(), salaryBaseItemize.getPayeeAcctNo(),
                salaryBaseItemize.getPayeeAcctBankName(), salaryBaseItemize.getIsDeleted(),salaryBaseItemize.getRemark()
        };
        this.getJdbcTemplate().update(sql.toString(), params);
    }

    @Override
    public void deleteSalaryBaseItemize(String id) {
        StringBuilder sql = new StringBuilder();
        //真删
        sql.append(" delete from ").append(TableName.THIRTEEN_SALARY_ITEMIZE)
                .append(" where agency_id=?");
        Object[] params = new Object[]{id};
        this.getJdbcTemplate().update(sql.toString(), params);
    }

    @Override
    public void deleteSalaryBaseItemizeAll(String province) {
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.THIRTEEN_SALARY_ITEMIZE)
                .append(" set update_time=?,is_deleted=? where mof_div_code=? ");
        Object[] params = new Object[]{
                String.valueOf(DateUtils.getCurrentTimeStamp()),SystemDictConstant.IS_DELETED,province
        };
        this.getJdbcTemplate().update(sql.toString(), params);
    }

    @Override
    public void updateSalaryBaseItemize(SalaryBaseItemize salaryBaseItemize) {
        String upId = "";
        StringBuilder sql = new StringBuilder();
        sql.append("update ").append(TableName.THIRTEEN_SALARY_ITEMIZE);
        sql.append(" set create_time=?, update_time=?, salary_base_id=?, salary_base_code=?, ")
                .append(" salary_base_name=?, person_state=?, person_state_name=?, mof_div_code=?, agency_id=?, ")
                .append(" agency_code=?, agency_name=?, payee_acct_id=?, payee_acct_name=?, payee_acct_no=?, ")
                .append(" payee_acct_bank_name=?, is_deleted=? ");
        if (!StringUtils.isEmpty(salaryBaseItemize.getId())) {
            sql.append(" where id = ? ");
            upId = salaryBaseItemize.getId();
        } else {
            sql.append(" where agency_id = ? ");
            upId = salaryBaseItemize.getAgencyId();
        }
        Object[] params = new Object[]{
                salaryBaseItemize.getCreateTime(), salaryBaseItemize.getUpdateTime(),
                salaryBaseItemize.getSalaryBaseId(), salaryBaseItemize.getSalaryBaseCode(),
                salaryBaseItemize.getSalaryBaseName(), salaryBaseItemize.getPersonState(),
                salaryBaseItemize.getPersonStateName(), salaryBaseItemize.getMofDivCode(), salaryBaseItemize.getAgencyId(),
                salaryBaseItemize.getAgencyCode(), salaryBaseItemize.getAgencyName(), salaryBaseItemize.getPayeeAcctId(),
                salaryBaseItemize.getPayeeAcctName(), salaryBaseItemize.getPayeeAcctNo(),
                salaryBaseItemize.getPayeeAcctBankName(), salaryBaseItemize.getIsDeleted(), upId
        };
        this.getJdbcTemplate().update(sql.toString(), params);
    }

    @Override
    public List<String> salaryBaseItemizeCheck(SalaryBaseItemize salaryBaseItemize) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select agency_id from ").append(TableName.THIRTEEN_SALARY_ITEMIZE).append(" where is_deleted = 0 ");
        if (!StringUtils.isEmpty(salaryBaseItemize.getSalaryBaseId())) {
            sql.append(" and salary_base_id = ? ");
            paramList.add(salaryBaseItemize.getSalaryBaseId());
        }
        if (!StringUtils.isEmpty(salaryBaseItemize.getPersonState())) {
            sql.append(" and person_state = ? ");
            paramList.add(salaryBaseItemize.getPersonState());
        }
        return super.getjdbcTemplate().queryForList(sql.toString(), paramList.toArray(), String.class);
    }

    /**
     * 银行 salaCardBank  和工资类别personState 过滤
     * @param childrenId
     * @param salaCardBank
     * @param personState
     * @return
     */
    @Override
    public List<AgencyInfo> queryAgencyInfo(List<String> childrenId,String salaCardBank,String personState) {
        List<String> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append("select DISTINCT a.*, b.agency_id, b.agency_address, b.zip, b.agency_adm_level_code,")
                .append(" b.adm_dep_type_code, b.agency_bank_name, b.agency_bank_cardno, b.central_agency_code,")
                .append(" b.agency_leader, b.is_uni_sala, b.remarks,b.has_file,b.fund_sup_code,")
                .append(" b.capital_type, b.effective_year, b.verify_state,is_payment_agency")
                .append(" from ").append(TableName.AGENCY_INFO).append(" a ")
                .append(" join ").append(TableName.AGENCY_EXT).append(" b ")
                .append(" on a.id = b.agency_id")
                .append(" join ").append(TableName.PERSON_EXT).append(" c ")
                .append(" on a.id = c.agency_id ")
                .append(" where a.is_deleted = '0'  and b.is_deleted='0'  and c.is_deleted='0' ");
        sql.append(" and ").append(SysUtils.inSql("a.id", childrenId));
        if (!StringUtils.isEmpty(personState)) {
            sql.append(" and c.merit_pay_code=? ");
            paramList.add(personState);
        }
        if (!StringUtils.isEmpty(salaCardBank)) {
            sql.append(" and c.sala_card_bank=? ");
            paramList.add(salaCardBank);
        }

        sql.append(" order by a.agency_code");
        //请求参数初始化
        return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper(AgencyInfo.class), paramList.toArray());
    }

}
